﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ShareFrame.Util.Helper
{
    public class DapperHelper<T>
    {
        #region 构造函数
        public static IConfigurationRoot Configuration { get; set; }
        private MySqlConnection conn;
        private string _ConnectionString;
        public DapperHelper(string ConnectionString)
        {
            _ConnectionString = ConnectionString;
        }
        public MySqlConnection GetMySqlConnection(int regattaId = 0, bool open = true,
          bool convertZeroDatetime = false, bool allowZeroDatetime = false)
        {
            conn = new MySqlConnection(_ConnectionString);
            return conn;
        }
        public void Dispose()
        {
            if (conn != null && conn.State != System.Data.ConnectionState.Closed)
            {
                conn.Close();
            }
        }
        #endregion

        #region 异步查询列表（无分页）
        /// <summary>
        /// 查询列表(无分页)
        /// </summary>
        /// <param name="sql">查询的sql</param>
        /// <param name="param">替换参数</param>
        /// <returns></returns>
        public async Task<PageResult<T>> QueryAsync(string sql, object param)
        {
            using (GetMySqlConnection())
            {
                if (conn.State == ConnectionState.Closed)
                {
                    await conn.OpenAsync();
                }
                var list = await conn.QueryAsync<T>(sql, param);
                return new PageResult<T> { Data = (List<T>)list, Total = list.Count() };
            }
        }
        #endregion

        #region 异步查询列表（分页）
        /// <summary>
        /// 异步查询列表（分页）
        /// </summary>
        /// <param name="sql">自己写的带分页的sql语句</param>
        /// <param name="param">自定义参数列表</param>
        /// <param name="sqlcount">自己写的获取总行数的sql语句</param>
        /// <returns></returns>
        public async Task<PageResult<T>> ExcuteSqlByPaging(string sql, object param, string sqlcount)
        {
            try
            {
                //此处可以根据传入的regattaId访问不同的数据库
                using (GetMySqlConnection())
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        await conn.OpenAsync();
                    }
                    int _count = conn.Query<int>(sqlcount, param, commandType: CommandType.Text).FirstOrDefault();
                    return new PageResult<T> { Data = ((List<T>)await conn.QueryAsync<T>(sql, param, commandType: CommandType.Text)), Total = _count };
                }
            }
            catch (Exception ex)
            {
                return new PageResult<T>()
                {
                    Success = false,
                    ErrorCode = 500,
                    Total = 0,
                    Msg = "Error:" + ex.Message
                };
            }
        }
        #endregion

        #region 查询第一条数据
        /// <summary>
        /// 查询第一个数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<T> QueryFirstAsync(string sql, object param)
        {
            using (GetMySqlConnection())
            {
                if (conn.State == ConnectionState.Closed)
                {
                    await conn.OpenAsync();
                }
                return await conn.QueryFirstOrDefaultAsync<T>(sql, param);
            }
        }
        #endregion

        #region 执行sql
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<int> ExecuteSqlAsync(string sql, object param)
        {
            using (GetMySqlConnection())
            {
                if (conn.State == ConnectionState.Closed)
                {
                    await conn.OpenAsync();
                }
                return await conn.ExecuteAsync(sql, param);
            }
        }
        #endregion

        #region Reader获取数据
        /// <summary>
        /// Reader获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<IDataReader> ExecuteReaderAsync(string sql, object param)
        {
            using (GetMySqlConnection())
            {
                if (conn.State == ConnectionState.Closed)
                {
                    await conn.OpenAsync();
                }
                return await conn.ExecuteReaderAsync(sql, param);
            }
        }
        #endregion

        #region Scalar获取数据
        /// <summary>
        /// Scalar获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<object> ExecuteScalarAsync(string sql, object param)
        {
            using (GetMySqlConnection())
            {
                if (conn.State == ConnectionState.Closed)
                {
                    await conn.OpenAsync();
                }
                return await conn.ExecuteScalarAsync(sql, param);
            }
        }
        #endregion

        #region 批量操作sql
        /// <summary>
        /// 批量操作sql
        /// </summary>
        /// <param name="Entities">实体类数组</param>
        /// <param name="BaseSql">基础sql语句</param>
        /// <returns></returns>
        public async Task BatchExcuteSql(List<T> Entities, string BaseSql)
        {
            try
            {
                //此处可以根据传入的regattaId访问不同的数据库
                using (GetMySqlConnection())
                {
                    await conn.ExecuteAsync(BaseSql, Entities);
                }
            }
            catch (Exception ex)
            {
                throw new BusException("系统报错：" + ex.Message);
            }
        }
        #endregion

        #region 事务提交
        /// <summary>
        /// 单个实体分字段插入多个表
        /// </summary>
        /// <param name="BaseSql">基础sql</param>
        /// <param name="Entity">实体</param>
        /// <returns></returns>
        public async Task ExcuteSqlByTran(List<string> BaseSql, T Entity)
        {
            try
            {
                //此处可以根据传入的regattaId访问不同的数据库
                using (GetMySqlConnection())
                {
                    conn.Open();
                    IDbTransaction transaction = conn.BeginTransaction();
                    try
                    {
                        for (int i = 0; i < BaseSql.Count(); i++)
                        {
                            await conn.ExecuteAsync(BaseSql[i], Entity, transaction);
                        }

                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new BusException("系统报错：" + ex.Message);
            }
        }
        #endregion       
    }
}
